package com.ldq.imooc.dao;

import com.ldq.imooc.bean.Message;
import com.ldq.imooc.db.DBAccess;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author : 李道泉
 * @Description :
 * @Date : 2018/8/7 16:50
 * @Modified by :
 */
public class MessageDao {

    /**
     * @Author : 李道泉
     * @Description : JDBC方式查询消息列表
     * @Date 2018/9/13 10:17
     * @Modified by :
     */
    public List<Message> queryMessageListjdbc(String command, String description) {

        List<Message> messageList = new ArrayList<>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/imooc?useSSL=false&serverTimezone=UTC", "root", "root");
            StringBuilder sql = new StringBuilder("select ID,COMMAND,DESCRIPTION,CONTENT from MESSAGE where 1=1");
            List<String> paramList = new ArrayList<String>();
            if (command != null && !"".equals(command.trim())) {
                sql.append(" and COMMAND=?");
                paramList.add(command);
            }
            if (description != null && !"".equals(description.trim())) {
                sql.append(" and DESCRIPTION like '%' ? '%'");
                paramList.add(description);
            }
            PreparedStatement statement = conn.prepareStatement(sql.toString());
            for (int i = 0; i < paramList.size(); i++) {
                statement.setString(i + 1, paramList.get(i));
            }
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                Message message = new Message();
                messageList.add(message);
                message.setId(rs.getString("ID"));
                message.setCommand(rs.getString("COMMAND"));
                message.setDescription(rs.getString("DESCRIPTION"));
                message.setContent(rs.getString("CONTENT"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return messageList;
    }

    /**
     * @Author : 李道泉
     * @Description : 根据查询条件查询消息列表
     * @Date 2018/9/13 10:17
     * @Modified by :
     */
    public List<Message> queryMessageList(String command, String description) {

        DBAccess dbAccess = new DBAccess();
        SqlSession sqlSession = null;
        List<Message> messageList = new ArrayList<>();
        try {
            sqlSession = dbAccess.getSqlSession();
            Message message = new Message();
            message.setCommand(command);
            message.setDescription(description);
            //通过SQLSession执行SQL语句
            messageList = sqlSession.selectList("Message.queryMessageList", message);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (sqlSession != null) {
                sqlSession.close();
            }
        }
        return messageList;
    }

    /**
     * @Author : 李道泉
     * @Description : 添加一条信息
     * @Date 2018/9/13 14:55
     * @Modified by :
     */
    public void insertOne(Message message) {

        DBAccess dbAccess = new DBAccess();
        SqlSession sqlSession = null;
        try {
            sqlSession = dbAccess.getSqlSession();
            //通过SQLSession执行SQL语句
            sqlSession.insert("Message.insertOne", message);
            sqlSession.commit();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (sqlSession != null) {
                sqlSession.close();
            }
        }
    }

    /**
     * @Author : 李道泉
     * @Description : 删除单条信息
     * @Date 2018/9/13 13:54
     * @Modified by :
     */
    public void deleteOne(int id) {

        DBAccess dbAccess = new DBAccess();
        SqlSession sqlSession = null;
        try {
            sqlSession = dbAccess.getSqlSession();
            //通过SQLSession执行SQL语句
            sqlSession.delete("Message.deleteOne", id);
            sqlSession.commit();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (sqlSession != null) {
                sqlSession.close();
            }
        }
    }

    /**
     * @Author : 李道泉
     * @Description : 批量删除
     * @Date 2018/9/13 15:50
     * @Modified by :
     */
    public void deleteBatch(List<Integer> ids) {

        DBAccess dbAccess = new DBAccess();
        SqlSession sqlSession = null;
        try {
            sqlSession = dbAccess.getSqlSession();
            //通过SQLSession执行SQL语句
            sqlSession.delete("Message.deleteBatch", ids);
            sqlSession.commit();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (sqlSession != null) {
                sqlSession.close();
            }
        }
    }

    public static void main(String[] args) {

        MessageDao messageDao = new MessageDao();
        messageDao.queryMessageList("", "");
        Logger logger;
//        logger.debug("1");
    }

}
